Release 10.1A: OpenEdge Development:
Programming Interfaces

Results lists

A results list is a list of ROWIDs that satisfy a query. The results list allows you to quickly access the records in the record set you define, and allows Progress to make the records available one at a time, as needed.

When more than one row satisfies a query, Progress doesn’t lock all of the records and hold them until you release them. Instead, when a specific record is needed, Progress uses the record’s ID to go directly to the record, locking only that record. By going directly to the record, Progress also ensures that you have the latest copy of the record.

When you open a query, Progress does not normally build the entire results list. Instead it initializes the results list and adds to it as needed. The NUM–RESULTS function returns the number of records currently in the results list. This is not necessarily the total number of records that satisfy the query.

Whether and when Progress builds the results list depends on the type of the query. As shown in Table 1–3, the DO or REPEAT PRESELECT statements always use a results list, while the FOR EACH and OPEN QUERY statements sometimes use a results list.

Queries have the following characteristics:

Scrolling versus non-scrolling — A query is scrolling if you specify SCROLLING in the DEFINE QUERY statement or if you define a browse for the query. You can use the REPOSITION statement to change your current position within the results list. For a non-scrolling query, you can only move sequentially through the rows by using the FIRST, LAST, NEXT, and PREV options of the GET statement. Scrolling queries must use a results list (often initially empty); non-scrolling queries might not.
Index-sorted — If the order of the rows in the query can be determined by using a single index, the query is index-sorted. For an index-sorted query, Progress can use the index to order records without a results list. However, if the query requires additional sorting it must also be presorted.
Presorted — If the query requires any sorting without an index or with multiple indexes, it must be presorted. For a presorted query, Progress must read all the records, sort them, and build the complete results list before any records are fetched.
Preselected versus non-preselected — You can force Progress to build a preselected results list by specifying PRESELECT on the OPEN QUERY, DO, or REPEAT statement.

Table 1–4 summarizes how the results list is built for each type of query.

Table 1–4: Results lists for specific query types 

Query type	Results list
Non-scrolling, index-sorted, no preselection	None.
Scrolling, no sorting, no preselection	Empty list1 established when query is opened. Records are added to the results list as needed.
Presorted or preselected	Complete list built when query is opened.

1If a browse is defined for the query, the results list initially contains one row.

There are two cases where Progress has to build the entire results list when you first open the query:

When you have explicitly used the PRESELECT option. In this case, Progress performs a preselection phase in which it reads each record of the query. You can specify the lock type to use during the preselection phase. (For an OPEN QUERY, the lock type you specify in the OPEN QUERY statement is used for the preselection phase.) These locks are released immediately unless the preselection occurs within a transaction.
When you have not used the PRESELECT option, but have specified sort criteria that cannot be performed using an index. In this case, Progress performs a presort phase in which it reads each record of the query with NO–LOCK and builds the results list.

For example, the following statement explicitly uses the PRESELECT option. This forces Progress to build the entire results list immediately:

OPEN QUERY cust-query PRESELECT EACH customer WHERE credit-limit > 1500.

If you had used FOR instead of PRESELECT, Progress would not have had to build the entire results list because it uses the primary index to fetch the records. It could use this index to find the first or last record for the query; it only needs to search forward or backward through the index until it finds a record that satisfies the WHERE clause.

You can use the PRESELECT option of the OPEN QUERY statement when you need to know immediately how many records satisfy the query or you can use it to immediately lock all the records that satisfy the query.

Progress also builds a complete results list when you open a query with a sort condition that cannot be resolved using a single index. Suppose you open a query on the customer table as follows:

OPEN QUERY cust-query FOR EACH customer BY city.

Because there is no index for the city field, Progress must retrieve all the records that satisfy the query (in this case, all the customer records), perform the sort, and build the entire results list before any records can be fetched. Until it performs this sort, Progress cannot determine the first or last record for the query. If an index were defined on the city field, Progress could use that index to fetch the records in sorted order (forwards or backwards) and would not need to build the results list in advance.

If the sort conditions for a query can be resolved using a single index, you can use the GET statement with the FIRST, LAST, NEXT, and PREV options on that query. For example, the following query is sorted using the primary index:

OPEN QUERY custqry FOR EACH customer. GET FIRST custqry. DISPLAY cust-num name.    /* Display first record */ PAUSE. GET NEXT custqry.         /* Display second record */ DISPLAY cust-num name. PAUSE. GET LAST custqry.         /* Display last record */ DISPLAY cust-num name. PAUSE. GET PREV custqry. DISPLAY cust-num name.   /* Display second-to-last record */

Because the sorting is done with a single index, you can move freely forwards and backwards within the query.

Note: If you want to use the REPOSITION statement on a query, you must make the query scrolling by specifying the SCROLLING option in a DEFINE QUERY statement.

Navigating a Results list

As shown in Table 1–3, results lists are associated with the OPEN QUERY and GET statements. However, Progress only guarantees a results list if you first define the query with the SCROLLING option:

DEFINE QUERY custqry FOR customer SCROLLING.

This option indicates to Progress that you want to use the results list for multi-directional navigation.

You can use the REPOSITION statement to specify how many places forward or backward you want to move, so that you can skip over a given number records. It also allows you to move to a specific ROWID.

The REPOSITION statement changes your location in the results list but does not actually fetch the record (unless the query is associated with a browse widget). To actually fetch records in a results list, you use the GET statement. The following example illustrates how the REPOSITION statement works:

DEFINE QUERY q FOR customer SCROLLING. DEFINE VARIABLE rid AS ROWID.       /* to save the ROWID of cust 4 */ OPEN QUERY q FOR EACH cust. GET NEXT q.                 /* gets cust no. 1 */ GET NEXT q.                 /* gets cust no. 2 */                             /* query is positioned ON cust 2 */ GET PREV q.                 /* gets cust no. 1 */ REPOSITION q FORWARD 0.     /* query is positioned BETWEEN cust 1 and 2 */ GET NEXT q.                 /* gets cust no. 2 */                             /* query is positioned ON cust 2 */ REPOSITION q FORWARD 1.     /* query is positioned BETWEEN cust 3 and 4 */ GET NEXT q.                 /* gets cust no. 4 */ rid = ROWID(cust).          /* query is positioned ON cust 4 */ REPOSITION q BACKWARD 2.    /* query is positioned BETWEEN cust 2 and 3 */ GET PREV q.                 /* gets cust no. 2 */ REPOSITION q TO ROWID(rid). /* query is positioned BETWEEN cust 3 and 4 */ GET NEXT q.                     /* gets cust no. 4 */

After a record is fetched (with a GET statement), the results list position is on the ROWID, so that GET NEXT gets the next record, and GET PREV gets the previous record. After a REPOSITION, the position is always between two records. Thus, REPOSITION FORWARD 0 repositions the results list immediately after the current record. GET NEXT fetches the next record; GET PREV fetches the previous record. REPOSITION FORWARD 1 repositions the results list between the next record and the record after it.

To find the total number of rows in a results list, you can use the NUM–RESULTS function. To find the current position within a results list, you can use the CURRENT–RESULT–ROW function.

As Table 1–3 shows, Progress also creates results lists for FOR EACH statements and for DO and REPEAT statements with the PRESELECT phrase. However, you cannot navigate freely through a results list created for the FOR EACH statement. If the results list was created for the FOR EACH statement, then Progress automatically steps through the results list in sorted order:

FOR EACH customer BY name:    DISPLAY name city state. END.

Within a PRESELECT block, you can use the FIND statement to move backwards and forwards through the results list:

/* This code fragment displays all customers in descending order */ DO PRESELECT EACH customer:   FIND LAST customer.    /* last position in list */   DISPLAY cust-num name WITH FRAME a DOWN.   REPEAT:     FIND PREV customer.  /* move backward through list */     DOWN WITH FRAME a.     DISPLAY cust-num name WITH FRAME a.   END. END.

Note: A powerful way of navigating a record set is with the browse widget. For more information, see OpenEdge Development: Progress 4GL Handbook .